﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Data.SqlClient;

/// <summary>
/// Summary description for PreparePromotionNew
/// </summary>
public class PreparePromotionNew
{
	public PreparePromotionNew()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    struct item
    {
        public string itemkey;
        public float invqty;
        public float price;
        //----------------sua 23/12/2011--------
        public float invamount;
        //public int BuyQuantity;
        public float curamount;
        //-------------------------------
        public float curqty;
        public string Operator;
        
    }
    struct Invoice
    {
        public float sumamount;
        public float sumqty;
    }
    private string strconn = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString + " uid=admin; pwd=baccamay";

    protected void InsertItemforPromontion(String[] orderdetail, string user,string connection)
    {
        string sql = "if exists (select name from dbo.sysobjects where name = '" + user + "INPUT') begin drop table [" + user + "INPUT] end";
        bool fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "CREATE TABLE [dbo].[" + user + "INPUT] ([itemkey] [varchar] (20) NOT NULL ,[Quantity] [float] NOT NULL ," +
            "[amount] [float] NULL,IPrice Float NULL,IVAT  Varchar(10) NULL,ILineDisc Float Null ) ON [PRIMARY]";
        fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        DataSet ds = new DataSet();
        for (int i = 3; i < orderdetail.Length; i+=2)
        {
            sql = "SELECT     price, saletaxkey FROM INMAST WHERE  itemkey = '"+orderdetail[i]+"'";
            ds = MyExecuteReader(sql, CommandType.Text, connection);
            if (ds.Tables[0].Rows.Count > 0)
            {
                string saletax = ds.Tables[0].Rows[0]["saletaxkey"].ToString();
                float price = float.Parse(ds.Tables[0].Rows[0]["price"].ToString());
                if (saletax.Length > 3)
                    price = price+price * float.Parse(saletax.Substring(3, saletax.Length - 3))/100;
                sql = "Insert Into [" + user + "INPUT] (itemkey,Quantity,amount,IPrice,ILineDisc,IVAT) " +
                    " Values('" + orderdetail[i] + "'," + orderdetail[i + 1] + "," + price * float.Parse(orderdetail[i + 1].ToString()) + "," + price + ",'','" + ds.Tables[0].Rows[0]["saletaxkey"] + "')";
                fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
            }
        }
        
    }

    public bool CallPromotion(string Order,string connection)
    {
        bool flg = false;
        try
        {            
            string []Orderdetail = Order.Split('.');
            string username = Orderdetail[0];
            string error = "";
            //-------------------------------
            string ngayhoadon = DateTime.Now.ToString("dd/MM/yyyy");
            string customerkey = Orderdetail[1];
            string[] date = ngayhoadon.Split('/');
            string Pdata = date[2] + "-" + date[1] + "-" + date[0];
            string sql = "if exists (select name from dbo.sysobjects where name = 'OUTPUT1" + username + "') begin drop table [OUTPUT1" + username + "] end";
            bool f = MyExecuteNonQuery(sql, CommandType.Text, connection);

            InsertItemforPromontion(Orderdetail, username, connection);
            bool fl1 = NewCalPromotion("promotion", username, Pdata, customerkey, "1", "", false, "I", connection);
            if (fl1)
            {
                BackupPromotion(username, connection);
            }
            InsertItemforPromontion(Orderdetail, username, connection);
            UpdateInput(username, connection);
            bool fl2 = NewCalPromotion("Pay Cash", username, Pdata, customerkey, "1", "", false, "I", connection);
            if (fl2)
            {
                BackupPromotion(username, connection);
            }
            InsertItemforPromontion(Orderdetail, username, connection);   
            //obj.UpdateInput(username, database, serverIP);
            bool fl = NewCalPromotion("ADD-IN", username, Pdata, customerkey, "1", "", false, "I", connection);
            if (fl1 || fl2)
            {
                RestoreBackupPromotion(username, connection);
            }
            sql = "Select '" + Order + "' from ARPRODEF where Program_ID=''";
            MyExecuteReader(sql, CommandType.Text, connection);
            sql = "Update [OUTPUT" + username + "] set LineDisc=1 WHERE     (ProgramID IN (SELECT Program_ID FROM  ARPRODEF  WHERE      (ObjType = 'Pay Cash')))";
            f = MyExecuteNonQuery(sql, CommandType.Text, connection);
            if (fl1 || fl2 || fl)
                flg = true;
        }
        catch
        {            
            return false;
        }
        return flg;
    }
    private bool NewCalPromotion(string Objtype, string UserName, string PDate, string Custkey, string chanel, string Program_ID, bool VanInvoice, string trntyp, string connection)
    {            
        string sql = "", error = "";
        bool fl = true,kq= false;        
        sql = "if exists (select name from dbo.sysobjects where name = '" + UserName + "PrOUT') begin drop table [" + UserName + "PrOUT] end";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "CREATE TABLE [" + UserName + "PrOUT] ([Itemkey] [varchar] (20) NULL ,[Qty] [float] NULL ,[Amt] [float] NULL ,[Disc] [float] NULL ,[ProgramID] [varchar] (20) NULL ,[PayProgramID] [varchar] (20) NULL ,   [ProgramType] [varchar] (10) NULL, Price Float Null, VAT varchar (10) null, LineDisc float null ) ON [PRIMARY] ";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "if exists (select name from dbo.sysobjects where name = '" + UserName + "PrOverAllowcate') begin drop table [" + UserName + "PrOverAllowcate] end";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "CREATE TABLE [" + UserName + "PrOverAllowcate] ([Program_ID] [varchar](50),[BuyLevel] [float] NULL) ON [PRIMARY] ";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "if exists (select name from dbo.sysobjects where name = '" + UserName + "_ArprTMP') begin drop table [" + UserName + "_ArprTMP] end";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "CREATE TABLE [dbo].[" + UserName + "_ArprTMP]([Program_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "if exists (select name from dbo.sysobjects where name = '" + UserName + "ARPRLIN') begin drop table [" + UserName + "ARPRLIN] end";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "CREATE TABLE [dbo].[" + UserName + "ARPRLIN]([Program_ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[itemkey] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[Quantity] [float] NULL,	[Price] [float] NULL,	[Tranno] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[Trntyp] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[InvDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[Customerkey] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[salespkey] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[Username] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[Disc] [float] NULL) ON [PRIMARY]";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        while (FindProgram(Objtype, "", UserName, false, PDate, Custkey, chanel, connection) && fl == true)
        {
            DataSet ds = new DataSet();
            sql = "SELECT     ProgramID FROM [" + UserName + "TMP1]";
            ds = MyExecuteReader(sql, CommandType.Text, connection);
            int i = 0;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (Pay(dr["ProgramID"].ToString(), UserName, VanInvoice, connection))
                {
                    i = i + 1;
                }
            }           
            sql = "update [OUTPUT" + UserName + "] set ProgramType = Program_type from ARPRODEF where Program_ID=ProgramID+PayProgramID";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
            deleteLoop(UserName, connection, Objtype);
            sql = "delete [OUTPUT" + UserName + "] where Qty=0";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
            //cap nhat lai INPUT
            sql = "Update [" + UserName + "INPUT] set amount = amount - amt, quantity = quantity - qty from [OUTPUT" + UserName + "]" + " where [" + UserName + "INPUT].Itemkey = [OUTPUT" + UserName + "].Itemkey and programID = '' ";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
            sql = "Delete [" + UserName + "INPUT] where quantity <= 0 or amount <=0 ";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
            sql = "insert into [" + UserName + "ARPRLIN](Program_ID, itemkey, Quantity, Price, Tranno, Trntyp, InvDate, Customerkey, Salespkey,UserName,Disc)" + " SELECT     PayProgramID, Itemkey, Qty, Price,'" + UserName + "Tranno' as Tranno, '" + trntyp + "' AS Trntyp, '" + PDate + "' AS invdate, '" + Custkey + "' AS  Customerkey, '' AS salespkey, '" + UserName + "'  as UserName,Disc FROM [OUTPUT" + UserName + "] where ProgramID=''";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
//            sql = "insert into [" + UserName + "PrOUT]( Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc) " + " SELECT     Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc FROM [OUTPUT" + UserName + "] where PayProgramID=''";
            sql = "insert into [" + UserName + "PrOUT]( Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc) " + " SELECT     Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc FROM [OUTPUT" + UserName + "]";

            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
            if(i==0)
            {
                fl= false;
            }
        }
        //sql = "insert into [OUTPUT" + UserName + "]( Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc) " + " SELECT     Itemkey, SUm(Qty) as Qty, Sum(Amt) As Amt, Sum(Disc) as Disc, ProgramID, PayProgramID, ARPRODEF.Program_Type, Price, VAT, sum(LineDisc) as LineDisc FROM [" + UserName + "PrOUT] INNER JOIN ARPRODEF ON ProgramID=program_ID where PayProgramID='' Group By ProgramID, Itemkey,PayProgramID, ARPRODEF.Program_Type, Price, VAT";
        sql = "insert into [OUTPUT" + UserName + "]( Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc) " + 
            " SELECT pr.Itemkey, SUM(pr.Qty) AS Qty, SUM(pr.Amt) AS Amt, SUM(pr.Disc) AS Disc, pr.ProgramID, pr.PayProgramID, ar.Program_type, pr.Price, pr.VAT, "+
            " SUM(pr.LineDisc) AS LineDisc FROM  [" + UserName + "PrOUT] AS pr INNER JOIN  ARPRODEF AS ar ON pr.ProgramID + pr.PayProgramID = ar.Program_ID GROUP BY pr.ProgramID, pr.Itemkey, pr.PayProgramID, ar.Program_type, pr.Price, pr.VAT";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "select top 1 * from [OUTPUT" + UserName + "]";
        if (MyExecuteReader(sql, CommandType.Text, connection).Tables[0].Rows.Count > 0)
            return true;
        else
            return false;
    }
    private void deleteLoop(string username, string connection, string Objtype)
    {
        string sql = "", error = "";
        string table = username + "TMP6";
        DataSet ds = new DataSet();
        sql = "CREATE TABLE [" + username + "TMP6] ([ProgramID] [varchar] (20) NULL ,[ProgramType] [varchar] (100) NULL,[Amount] [float] NULL,[PayAmount] [float] NULL ,[SKUCode] [varchar] (20) NULL,[Priority] [int] NULL ) ON [PRIMARY] ";
        bool fl = MyExecuteNonQuery(sql, CommandType.Text, connection);

        sql = "SELECT     out.ProgramID,out.BuyLevel FROM         [OUTPUT" + username + "] AS out INNER JOIN  ARPROAllocate AS arall ON out.ProgramID = arall.Program_ID WHERE     (arall.AllocateVal - (arall.ActualPromotion + out.Amt) < 0)";
        ds = MyExecuteReader(sql, CommandType.Text, connection);
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            sql = "delete from [OUTPUT" + username + "] where ProgramID + PayProgramID='" + dr["ProgramID"].ToString() + "'";
            fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
            sql = "insert into [" + username + "PrOverAllowcate](Program_ID,BuyLevel) values('" + dr["ProgramID"].ToString() + "'," + dr["BuyLevel"].ToString() + ")";
            fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        }
        sql = "SELECT Itemkey FROM [OUTPUT" + username + "] WHERE (ProgramID = '') GROUP BY Itemkey HAVING (COUNT(ProgramID) > 1)";
       
        ds = MyExecuteReader(sql, CommandType.Text, connection);
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            //avg();
            sql = "insert into  [" + username + "TMP6](ProgramID, Amount,ProgramType, SKUCode) SELECT  PayProgramID, avg(Disc) AS sumamount,ProgramType," +
                " '" + dr["Itemkey"].ToString() + "' AS itemkey FROM [OUTPUT" + username + "] WHERE  (ProgramID = '') AND (Itemkey = '" + dr["Itemkey"].ToString() + "') GROUP BY PayProgramID,ProgramType";
            fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        }
        string txt = "";
        sql = "update  [" + username + "TMP6] set [" + username + "TMP6].Priority = Focus from ARPRODEF where [" + username + "TMP6].ProgramID = ARPRODEF.Program_ID";
        fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        
        DataSet Rs = new DataSet();
        sql = "select SKUCode,ProgramID,Amount from [" + username + "TMP6] order by skucode ,Priority DESC,amount DESC";
        if (Objtype != "promotion")
            sql = "select SKUCode,ProgramID,Amount from [" + username + "TMP6] order by skucode DESC, Priority DESC, amount";
        Rs = MyExecuteReader(sql, CommandType.Text, connection);
        int rn = 0;
        foreach (DataRow dr in Rs.Tables[0].Rows)
        {
            //string txt2 = dt.MyExecuteQuerys(ref error, "set_txt1", CommandType.StoredProcedure, new SqlParameter("@database", database), new SqlParameter("@ProgramID", Rs.Tables[0].Rows[ii]["ProgramID"].ToString()));
            if (txt != dr["SKUCode"].ToString())
            {
                txt = dr["SKUCode"].ToString();
            }
            else
            {
                //isExist = dt.MyExecuteNonQuery("delete_OUTPUT", CommandType.StoredProcedure, ref error, new SqlParameter("@database", database), new SqlParameter("@usernames", username), new SqlParameter("@ProgramID", Rs.Tables[0].Rows[ii]["ProgramID"].ToString()));
                sql = "delete from [OUTPUT" + username + "] where ProgramID + PayProgramID='" + dr["ProgramID"].ToString() + "'";
                fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
            }
        }
    }
    private bool FindProgram(string objtype, string Program_ID, string UserName, bool PromotionByCustomer, string PDate, string Custkey, string login_chanel, string connection)
    {
        bool fl = false,kq= false;
        string error ="", sql ="";

        for(int i=1;i<7;i++)
        {
            sql = "if exists (select name from dbo.sysobjects where name = '" + UserName + "TMP" + i + "') begin drop table [" + UserName + "TMP" + i + "] end";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        }
        sql = "if exists (select name from dbo.sysobjects where name = 'OUTPUT" + UserName + "') begin drop table [OUTPUT" + UserName + "] end";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);

        sql = "CREATE TABLE [OUTPUT" + UserName + "] ([Itemkey] [varchar] (20) NULL ,[Qty] [float] NULL ,[Amt] [float] NULL ,[Disc] [float] NULL ,[ProgramID] [varchar] (20) NULL ,[PayProgramID] [varchar] (20) NULL ,   [ProgramType] [varchar] (10) NULL, Price Float Null, VAT varchar (10) null, LineDisc float null,BuyLevel Float Null ) ON [PRIMARY] ";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);

        if(objtype.ToUpper() == "ADD-IN")
        {
            sql = "Select Program_ID as ProgramID, Program_Des as Pr_Description, Program_Type as PrType, SelfPromotion, " + 
                " Priority, AddIn, [From] as PFrom, [To] as PTo, Active, Obj, PrintOut  into [" + UserName + "TMP1]  from ARProDef " + 
                " where  UPPER(ObjType)='ADD-IN' and active = 1  and [From] <= '" + PDate + "'  and [To] >= '" + PDate + "' And Left(Program_Type,4) <> 'NONE' " + " And Program_ID In(select Program_ID from ARProDefChanel where chanel ='" + login_chanel + "')  and Program_ID in (SELECT DISTINCT ProBuyDef.Program_ID FROM  ProBuyDef INNER JOIN " + " [" + UserName + "INPUT] ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey) ";
            //sql = "Select Program_ID as ProgramID, Program_Des as Pr_Description, Program_Type as PrType, SelfPromotion, " + " Priority, AddIn, [From] as PFrom, [To] as PTo, Active, Obj, PrintOut  into " + UserName + "TMP1  from ARProDef " + " where Program_ID='" + Program_ID + "' And UPPER(ObjType)='ADD-IN' and active = 1  And Left(Program_Type,4) <> 'NONE' " + " And Program_ID In(select Program_ID from ARProDefChanel where chanel ='" + login_chanel + "')  and Program_ID in (SELECT DISTINCT ProBuyDef.Program_ID FROM  ProBuyDef INNER JOIN " + " [" + UserName + "INPUT] ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey) ";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        }
        else
        {
            if(objtype.ToUpper() == "PROMOTION" )
            {
                sql = "Select Program_ID as ProgramID, Program_Des as Pr_Description, Program_Type as PrType, SelfPromotion, " + " Priority, AddIn, [From] as PFrom, [To] as PTo, Active, Obj, PrintOut  into [" + UserName + "TMP1]  from ARProDef " + " where UPPER(ObjType)='PROMOTION'   and active = 1  and [From] <= '" + PDate + "'  and [To] >= '" + PDate + "' and Left(Program_Type,4) <> 'NONE' " + " And Program_ID In(select Program_ID from ARProDefChanel where chanel ='" + login_chanel + "')   and Program_ID in (SELECT DISTINCT ProBuyDef.Program_ID FROM  ProBuyDef INNER JOIN " + " [" + UserName + "INPUT] ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey) ";
                kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
            }
            else
            {
                if(objtype.ToUpper() == "PAY CASH")
                {
                    sql = "Select Program_ID as ProgramID, Program_Des as Pr_Description, Program_Type as PrType, SelfPromotion, " + " Priority, AddIn, [From] as PFrom, [To] as PTo, Active, Obj, PrintOut  into [" + UserName + "TMP1]  from ARProDef " + " where  UPPER(ObjType)='PAY CASH'  and active = 1  and [From] <= '" + PDate + "'  and [To] >= '" + PDate + "' and Left(Program_Type,4) <> 'NONE' " + " And Program_ID In(select Program_ID from ARProDefChanel where chanel ='" + login_chanel + "')   and Program_ID in (SELECT DISTINCT ProBuyDef.Program_ID FROM  ProBuyDef INNER JOIN " + " [" + UserName + "INPUT] ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey) ";
                    kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
                }
            }
        }
        sql = " Insert into [" + UserName + "INPUT](itemkey, Quantity, amount, IPrice)  SELECT     itemkey, 0 AS Quantity, 0 AS amount, 0 AS IPrice" + " FROM   ProBuyDef WHERE (Program_ID IN (SELECT  ProgramID FROM  [" + UserName + "TMP1])) AND (NOT (itemkey IN" + " (SELECT itemkey FROM [" + UserName + "INPUT])))";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);

        //kiem tra chuong trinh quy dinh so luong mua
        sql = "Select * from [" + UserName + "_ArprTMP]";
        DataTable dt = MyExecuteReader(sql, CommandType.Text, connection).Tables[0];
        if (dt.Rows.Count > 0)
        {
            sql = "DELETE FROM " + UserName + "TMP1 WHERE     (ProgramID IN (SELECT     ProBuyDef.Program_ID FROM   ProBuyDef INNER JOIN [" + UserName + "INPUT]" +
                " ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey AND ProBuyDef.BuyQuantity > [" + UserName + "INPUT].Quantity" +
                " WHERE (ProBuyDef.Program_ID IN (SELECT     ProgramID FROM " + UserName + "TMP1)))) and ProgramID not in(select Program_ID from [" + UserName + "_ArprTMP] )";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        }
        else
        {
            sql = "DELETE FROM " + UserName + "TMP1 WHERE     (ProgramID IN (SELECT     ProBuyDef.Program_ID FROM          ProBuyDef INNER JOIN [" + UserName + "INPUT]" + " ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey AND ProBuyDef.BuyQuantity > [" + UserName + "INPUT].Quantity" + " WHERE (ProBuyDef.Program_ID IN (SELECT     ProgramID FROM " + UserName + "TMP1))))";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
            sql = "insert into [" + UserName + "_ArprTMP]( Program_ID) select  ProgramID From [" + UserName + "TMP1] where ProgramID in(Select Program_ID from ProBuyDef where BuyQuantity>0) and ProgramID not in(Select Program_ID from ProBuyDef where Operator=1)";
            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        }
        
        sql = "Select Program_ID Into [" + UserName + "TMP2]  From ARPRCUSTOMER Where customerKey ='" + Custkey + "'  and chanel=" + login_chanel + "Group By Program_ID";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "Select Program_ID Into [" + UserName + "TMP3]  From ARPRCUSTOMER Where Program_ID Not In (Select Program_ID From [" + UserName + "TMP2])";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);

        sql = "Delete [" + UserName + "TMP1] Where ProgramID In (Select Program_ID From [" + UserName + "TMP3])";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "Delete [" + UserName + "INPUT] where Quantity=0";
        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql="select * from [" + UserName + "TMP1]";
        dt = MyExecuteReader(sql, CommandType.Text, connection).Tables[0];
        if(dt.Rows.Count>0)
            fl= true;
        else
            fl= false;
        return fl;

    }
    private Invoice ExtractListItem(bool CalByQuantity, ArrayList listitem, string ProID, string UserName, string connection)
    {
        Invoice Invoice = new Invoice();
        Invoice.sumamount = 0;
        Invoice.sumqty = 0;
        //--------------sua 23/12/2011
        string error = "", sql = "SELECT     ProBuyDef.itemkey, ProBuyDef.Operator, ProBuyDef.BuyQuantity, Quantity as qty,IPrice,amount" +
        " FROM  ProBuyDef INNER JOIN   [" + UserName + "INPUT] ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey WHERE     (ProBuyDef.Program_ID = '" + ProID + "') " +
        " ORDER BY ProBuyDef.Operator DESC, ProBuyDef.BuyQuantity*IPrice DESC, IPrice, Quantity,amount DESC";
        if (CalByQuantity)
        {
            sql = "SELECT     ProBuyDef.itemkey, ProBuyDef.Operator, ProBuyDef.BuyQuantity, Quantity as qty,IPrice,amount" +
        " FROM  ProBuyDef INNER JOIN   [" + UserName + "INPUT] ON ProBuyDef.itemkey = [" + UserName + "INPUT].itemkey WHERE     (ProBuyDef.Program_ID = '" + ProID + "') " +
        " ORDER BY ProBuyDef.Operator DESC, ProBuyDef.BuyQuantity DESC, IPrice, Quantity,amount DESC";
        }
        //---------------------------------
        DataSet ds = new DataSet();
        ds = MyExecuteReader(sql, CommandType.Text, connection);
        int itemnum = ds.Tables[0].Rows.Count;
        item item = new item();
        for (int i = 0; i < itemnum; i++)
        {
            item.itemkey = ds.Tables[0].Rows[i]["itemkey"].ToString();
            item.Operator = ds.Tables[0].Rows[i]["Operator"].ToString();
            item.price = float.Parse(ds.Tables[0].Rows[i]["IPrice"].ToString());
            if (CalByQuantity)
                item.invqty = (int)(float.Parse(ds.Tables[0].Rows[i]["qty"].ToString()));
            else
                item.invqty = float.Parse(ds.Tables[0].Rows[i]["qty"].ToString());
            //Chinh lai phan loc chuong trinh co muc mua >0 trong phan khai bao mua
            if (item.Operator == "0" || item.Operator.ToUpper() == "FALSE")
            {
                item.curqty = 0;
                item.curamount = 0;
            }
            else
            {
                item.curqty = int.Parse(ds.Tables[0].Rows[i]["BuyQuantity"].ToString());
                item.curamount = item.curqty * item.price;
            }
            //--------------sua 23/12/2011------------
            item.invamount = float.Parse(ds.Tables[0].Rows[i]["amount"].ToString());
            //item.BuyQuantity = int.Parse(ds.Tables[0].Rows[i]["BuyQuantity"].ToString());

            //--------------------------------------
            listitem.Add(item);
            if (item.invqty > 0)
            {
                //-----------------sua 23/12/2011--------------
                Invoice.sumamount = Invoice.sumamount + item.invamount;
                //---------------------------------------------
                Invoice.sumqty = Invoice.sumqty + item.invqty;
            }
        }
        return Invoice;
    }
    private bool Pay(string ProgramID, string UserName, bool VanInvoice, string connection)
    {
        bool fl = false,kq=false;
        string error = "", sql ="";
        DataSet ds = new DataSet();        
        if(VanInvoice)
            sql = "SELECT     Program_ID, BuyLevel, CalByQuantity, PayItemkey,Case when PayPrice='' or PayPrice is null then 0 else PayPrice End as PayPrice, PayQuantity, PayType FROM  ProPayDef where Program_ID='" + ProgramID + "' And IsDirectSale=1 ORDER BY BuyLevel DESC";
        else
            sql = "SELECT     Program_ID, BuyLevel, CalByQuantity, PayItemkey, Case when PayPrice='' or PayPrice is null then 0 else PayPrice End  as PayPrice , PayQuantity, PayType FROM  ProPayDef where Program_ID='" + ProgramID + "' ORDER BY BuyLevel DESC";

        ds = MyExecuteReader(sql, CommandType.Text, connection);
        for (int y = 0; y < ds.Tables[0].Rows.Count; y++)
        {
            ArrayList listitem = new ArrayList();
            bool CalByQuantity = bool.Parse(ds.Tables[0].Rows[y]["CalByQuantity"].ToString());
            Invoice inv = ExtractListItem(CalByQuantity, listitem, ProgramID, UserName, connection);
            float ProCounts = float.Parse(ds.Tables[0].Rows[y]["BuyLevel"].ToString());
            sql = "select Program_ID from [" + UserName + "PrOverAllowcate] where Program_ID='" + ProgramID + "' and BuyLevel=" + ProCounts;
            DataSet dsover = MyExecuteReader(sql, CommandType.Text, connection);
            if (dsover.Tables[0].Rows.Count < 1)
            {
                int PayType = int.Parse(ds.Tables[0].Rows[y]["PayType"].ToString());
                if ((CalByQuantity && inv.sumqty >= ProCounts) || (CalByQuantity == false && inv.sumamount >= ProCounts))
                {
                    int fl1 = -1;
                    fl1 = Combipack(0, (item)listitem[0], ProCounts, 0, listitem.Count, listitem, CalByQuantity, connection);
                    if (fl1 > -1)
                    {
                        float amount = 0;
                        for (int i = 0; i < listitem.Count; i++)
                        {
                            item items = (item)listitem[i];
                            if (PayType == 2)
                            {
                                sql = "insert into [OUTPUT" + UserName + "](Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc)" + " values('" + items.itemkey + "'," + items.invqty + "," + items.invqty * items.price + "," + ds.Tables[0].Rows[y]["PayQuantity"] + ",'','" + ProgramID + "',''," + items.price + ",'','')";
                                amount = amount + items.invqty * items.price;
                            }
                            else
                            {
                                //---------sua 23.12.2011----------
                                sql = "insert into [OUTPUT" + UserName + "](Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc)" + " values('" + items.itemkey + "'," + items.curqty + "," + items.curamount + "," + ds.Tables[0].Rows[y]["PayQuantity"] + ",'','" + ProgramID + "',''," + items.price + ",'','')";
                                amount = amount + items.curamount;
                                //------------------------
                            }
                            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
                        }
                        //neu chuong trinh tra bang chiet khau
                        if (PayType == 2)
                        {
                            sql = "insert into [OUTPUT" + UserName + "](Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc)" +
                                " values('" + ds.Tables[0].Rows[y]["PayItemkey"] + "'," + ds.Tables[0].Rows[y]["PayQuantity"] + "," + amount * 0.01 * float.Parse(ds.Tables[0].Rows[y]["PayQuantity"].ToString()) + "," + ds.Tables[0].Rows[y]["PayQuantity"] + ",'" + ProgramID + "','','',0,'','')";
                            kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
                        }
                        else
                        {
                            if (PayType == 0)
                            {
                                sql = "SELECT     TOP (1) PayOption, Program_ID,itemkey, SUM(Price * PayQuantity) AS amt, BuyLevel FROM  ARPROPAYITEM  GROUP BY PayOption, Program_ID," +
                                    " BuyLevel,itemkey HAVING      (Program_ID = '" + ProgramID + "') AND (BuyLevel = " + ProCounts + ") ORDER BY PayOption";
                                DataSet dss = new DataSet();
                                dss = MyExecuteReader(sql, CommandType.Text, connection);

                                sql = "insert into [OUTPUT" + UserName + "](Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc)" +
                                   " values('" + dss.Tables[0].Rows[0]["itemkey"] + "'," + ds.Tables[0].Rows[y]["PayQuantity"] + "," + dss.Tables[0].Rows[0]["amt"].ToString() + "," + (float.Parse(dss.Tables[0].Rows[0]["amt"].ToString()) / amount * 100).ToString() + ",'" + ProgramID + "','','',0,'','')";
                                //sql = "insert into [" + UserName + "OUTPUT](Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc)" +
                                //" SELECT     ARPROPAYITEM.itemkey, ARPROPAYITEM.PayQuantity * ProPayDef.PayQuantity AS Qty, ARPROPAYITEM.PayQuantity * ProPayDef.PayQuantity * ARPROPAYITEM.Price AS Amt," +
                                //    " 0 AS Disc, ARPROPAYITEM.Program_ID, '' AS PayProgramID, '' AS ProgramType, ARPROPAYITEM.Price, '' AS VAT, '' AS LineDisc FROM     ARPROPAYITEM INNER JOIN " +
                                //    " ProPayDef ON ARPROPAYITEM.Program_ID = ProPayDef.Program_ID AND ARPROPAYITEM.BuyLevel = ProPayDef.BuyLevel WHERE     (ProPayDef.BuyLevel = " + ProCounts + " and ARPROPAYITEM.Program_ID='" + ProgramID + "')";
                                kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
                                sql = "Update [OUTPUT" + UserName + "] set Disc=" + (float.Parse(dss.Tables[0].Rows[0]["amt"].ToString()) / amount * 100).ToString() + " where ProgramID+PayProgramID='" + ProgramID + "'";
                                kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
                            }
                            else
                            {
                                sql = "insert into [OUTPUT" + UserName + "](Itemkey, Qty, Amt, Disc, ProgramID, PayProgramID, ProgramType, Price, VAT, LineDisc)" +
                                    " values('" + ds.Tables[0].Rows[y]["PayItemkey"] + "',1," + ds.Tables[0].Rows[y]["PayQuantity"] + "," + (float.Parse(ds.Tables[0].Rows[y]["PayQuantity"].ToString()) / amount * 100).ToString() + ",'" + ProgramID + "','','',0,'','')";
                                kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
                                sql = "Update [OUTPUT" + UserName + "] set Disc=" + (float.Parse(ds.Tables[0].Rows[y]["PayQuantity"].ToString()) / amount * 100).ToString() + " where ProgramID+PayProgramID='" + ProgramID + "'";
                                kq = MyExecuteNonQuery(sql, CommandType.Text, connection);

                            }
                        }
                        sql = "Update [OUTPUT" + UserName + "] set BuyLevel=" + ds.Tables[0].Rows[y]["BuyLevel"].ToString() + " where ProgramID+PayProgramID='" + ProgramID + "'";
                        kq = MyExecuteNonQuery(sql, CommandType.Text, connection);
                        fl = true;
                        break;
                    }
                    else
                        fl = false;
                }
                else
                {
                    fl = false;
                }
            }
            else
            {
                fl = false;
            }
        }
        return fl;
    }
   
    private int Combipack(float Sum, item item, float Procounts, int itemnum, int maxitemnum, ArrayList listitem, bool CalByQuantity, string connection)
    {
        float price = item.price;
        if (price == 0)
        {
            price = 1;
        }
        if (item.curqty == 0)
        {
            float tmp1 = (item.invqty - item.curqty);
            //-------sua 23/12/2011------------------------------
            if (CalByQuantity == false)
            {
                tmp1 = item.invamount - item.curamount;
            }
            //------------------------------
            float tmp2 = Sum;
            tmp2 = tmp2 + tmp1;
            //-----------sua 23/12/2011----------------
            if (tmp2 - Procounts > 0)
            {
                tmp1 = tmp1 - tmp2 + Procounts;
            }
            else
            {
                if (CalByQuantity == true)
                    tmp1 = item.invqty;
                else
                    tmp1 = item.invamount;
            }
            if (CalByQuantity == true)
            {
                item.curamount = tmp1 * item.price;
                item.curqty = tmp1;
            }
            else
            {
                item.curqty = tmp1 / item.price;
                item.curamount = tmp1;
            }
            item tmp = (item)listitem[itemnum];
            tmp.curqty = item.curqty;
            tmp.curamount = item.curamount;
            listitem[itemnum] = tmp;
        }
        if (CalByQuantity == true)
            Sum = Sum + item.curqty;
        else
            Sum = Sum + item.curamount;
        //---------------------------------------------
        int total = itemnum;
        if (Sum >= Procounts || item.curqty > Procounts)
            return total;
        else
        {
            if (itemnum == maxitemnum - 1)
            {
                //----------sua 23.12.2011----------
                if ((item.curqty < item.invqty &&  item.curamount < item.invamount) && (item.Operator == "0" || item.Operator.ToUpper() == "FALSE"))
                {
                    //itemnum = itemnum - 1;
                    item = (item)listitem[itemnum];
                    if (CalByQuantity == true)
                        Sum = Sum - item.curqty;
                    else
                        Sum = Sum - item.curqty * item.price;
                    item.curqty = item.curqty + 1;
                    item.curamount = item.curamount + item.price * 1;
                //------------
                    listitem[itemnum] = item;
                    return Combipack(Sum, (item)listitem[itemnum], Procounts, itemnum, maxitemnum, listitem, CalByQuantity, connection);
                }
                else
                {
                    itemnum = itemnum - 1;
                    if (itemnum == -1)
                    {
                        return -1;
                    }
                    else
                    {
                        item = (item)listitem[itemnum];
                        if (CalByQuantity == true)
                            Sum = Sum - item.curqty;
                        else
                            Sum = Sum - item.curqty * item.price;
                        return Combipack(Sum, (item)listitem[itemnum], Procounts, itemnum, maxitemnum - 1, listitem, CalByQuantity, connection);
                    }
                }
            }
            else
            {
                itemnum = itemnum + 1;
                return Combipack(Sum, (item)listitem[itemnum], Procounts, itemnum, maxitemnum, listitem, CalByQuantity, connection);
            }
        }
        return total;
    }
    private void BackupPromotion(string username, string connection)
    {
        bool fl = false;
        string error = "";
        string sql = "if exists (select name from dbo.sysobjects where name = 'OUTPUT1" + username + "') begin drop table [OUTPUT1" + username + "] end";
        //fl = MyExecuteNonQuery(sql, CommandType.Text, "", "");
        sql = "select * into [OUTPUT1" + username + "] from [OUTPUT" + username + "]  WHERE     (ProgramType = 'NO')";
        fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "insert into [OUTPUT1" + username + "] select *  from [OUTPUT" + username + "] where PayProgramID=''";
        fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "update [OUTPUT" + username + "] set amt=price*qty*disc/100 where ProgramID='' and (RIGHT(ProgramType, 2) IN ('CK', 'TT'))";
        fl = MyExecuteNonQuery(sql, CommandType.Text, connection);
        sql = "insert into [OUTPUT1" + username + "] select *  from [OUTPUT" + username + "] where ProgramID='' and (RIGHT(ProgramType, 2) IN ('CK', 'TT'))";
        fl = MyExecuteNonQuery(sql, CommandType.Text, connection);

    }
    private void UpdateInput(string UserName, string connection)
    {
        bool fl = false;
        DataSet ds = new DataSet();
        string sql = "SELECT     ProgramID, RIGHT(ProgramType, 2) AS ProgramType FROM [OUTPUT1" + UserName + "] WHERE     (RIGHT(ProgramType, 2) = 'CK') OR (RIGHT(ProgramType, 2) = 'TT')";
        ds = MyExecuteReader(sql, CommandType.Text, connection);
        try
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (dr["ProgramType"].ToString() == "CK")
                {
                    sql = "Update [" + UserName + "INPUT] set IPrice=(IPrice-Price*(Disc*ARPRLIN.Quantity/[" + UserName + "INPUT].Quantity)/100) from [" + UserName + "ARPRLIN] ARPRLIN where ARPRLIN.Program_ID='" + dr["ProgramID"].ToString() + "' and ARPRLIN.itemkey=[" + UserName + "INPUT].itemkey";
                    //sql = "Update " + UserName + "INPUT set IPrice=((IPrice-Price*Disc/100)*ARPRLIN.Quantity+IPrice*" + UserName + "INPUT.Quantity)/(ARPRLIN.Quantity+" + UserName + "INPUT.Quantity) from " + UserName + "ARPRLIN ARPRLIN where ARPRLIN.Program_ID='" + dr["ProgramID"].ToString() + "' and ARPRLIN.itemkey=[" + UserName + "INPUT].itemkey";
                }
                else
                {
                    sql = "SELECT  ROUND(Disc / (SUM(Quantity * Price)/100), 3) AS Disc FROM   [" + UserName + "ARPRLIN] GROUP BY Program_ID, Disc HAVING      (Program_ID = '" + dr["ProgramID"].ToString() + "')";
                    DataTable dt = new DataTable();
                    dt = MyExecuteReader(sql, CommandType.Text, connection).Tables[0];
                    sql = "Update " + UserName + "INPUT set IPrice=IPrice-" + dt.Rows[0]["Disc"].ToString() + "*IPrice/100  from " + UserName + "ARPRLIN ARPRLIN where ARPRLIN.Program_ID='" + dr["ProgramID"].ToString() + "' and ARPRLIN.itemkey=[" + UserName + "INPUT].itemkey";
                }
                MyExecuteNonQuery(sql, CommandType.Text, connection);
            }
        }
        catch { return; }

    }
    private void RestoreBackupPromotion(string username, string connection)
    {
        string error = "";

        bool fl = false;
        string sql = "if exists (select name from dbo.sysobjects where name = 'OUTPUT" + username + "') begin insert into [OUTPUT" + username + "] select * from [OUTPUT1" + username + "] end";
        fl = MyExecuteNonQuery(sql, CommandType.Text, connection);

    }

    private bool MyExecuteNonQuery(string strSQL, CommandType ct,string connection)
    {
        string error = "";
        string connstr = connection;
        using (SqlConnection Conn = new SqlConnection(connstr))
        {
            using (SqlCommand Comm = new SqlCommand(strSQL, Conn))
            {
                bool f = false;
                Comm.Parameters.Clear();
                Comm.CommandText = strSQL;
                Comm.CommandType = ct;
                //Array.ForEach(param, paramItem => Comm.Parameters.Add(paramItem));
                try
                {
                    Conn.Open();
                    Comm.ExecuteNonQuery();
                    f = true;
                }
                catch (SqlException ex)
                {
                    error = ex.Message;
                }
                finally
                {
                    Conn.Close();
                }
                return f;
            }
        }
    }
    private DataSet MyExecuteReader(string strSQL, CommandType ct, string connection)
    {
        string error = "";
        string connstr = connection;
        using (SqlConnection Conn = new SqlConnection(connstr))
        {
            using (SqlCommand Comm = new SqlCommand(strSQL, Conn) { CommandType = ct })
            {
                using (SqlDataAdapter adp = new SqlDataAdapter(Comm))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        adp.Fill(ds);
                    }
                    catch (SqlException ex)
                    {
                        error = ex.Message;
                    }
                    return ds;
                }
            }
        }
    }
    
}
